/**
 *
 */
package com.evil.DAO;

import com.evil.Bean.AnswerOverItemBean;
import com.evil.Bean.OverAnswerBean;
import com.evil.DB_Utils.DB_Utils;
import com.evil.Data.Data;
import com.evil.Utils.PropertiesUtils;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/******************************************
 * 工程名：EdaSupport<br>
 * 包名：com.evil.DAO<br>
 * 时间：2016年9月1日 下午6:59:28<br>
 * 作者：白雨濃 evil<br>
 * 邮箱：evilbai@foxmail.com<br>
 * 内容：已经答题完成的题目DAO<br>
 ******************************************/
public class AnswerOverItemDAO
{
    public static final int NULL = 0;
    public static final int FAIL = -1;
    public static final int SUCCESS = 1;

    private int flag;
    private AnswerOverItemBean overItemBean;
    private String reErrorInfor;

    private ArrayList<OverAnswerBean> overAnswerBeenList;

    private Map<String, String> userInfo;

    /**
     * 插入新条目
     */
    public Object insertNewItem(AnswerOverItemBean bean)
    {
        final String insertUrl = "INSERT INTO `edasupport`.`useroveritem` (`phone`, `item_id`, `yes`, `no`, `flow` ,`overtime`) VALUES (?, ?, ?, ?, ?, ?);";
        final String findUrl = "SELECT\n" + "useroveritem.id,\n"
                + "useroveritem.phone,\n" + "useroveritem.item_id,\n"
                + "useroveritem.yes,\n" + "useroveritem.`no`,\n"
                + "useroveritem.flow,\n" + "useroveritem.overtime\n" + "FROM\n"
                + "useroveritem\n" + "WHERE\n" + "useroveritem.phone = ? AND\n"
                + "useroveritem.item_id = ?";

        DB_Utils.query(findUrl).add(bean.getPhone()).add(bean.getItemId())
                .build().get(next ->
        {
            if (next.next())
            {
                overItemBean = new AnswerOverItemBean();
                overItemBean.setPhone(next.getString(2));
                overItemBean.setItemId(next.getInt(3));
                overItemBean.setYesNum(next.getInt(4));
                overItemBean.setNoNum(next.getInt(5));
                overItemBean.setSumFlow(next.getInt(6));
                overItemBean.setOvertime(next.getDateTime(7));
                flag = FAIL;
            }
            else
            {
                flag = NULL;
            }
        }).close();

        if (flag == NULL)
        {
            DB_Utils.insert(insertUrl).addAll(add ->
            {
                add.add(1, bean.getPhone());
                add.add(2, bean.getItemId());
                add.add(3, bean.getYesNum());
                add.add(4, bean.getNoNum());
                add.add(5, bean.getSumFlow());
                add.add(6, new Date());
                reErrorInfor = add.build();
            }).close();


            if (reErrorInfor != null)
                if (reErrorInfor.equals("1"))
                {
                    flag = SUCCESS;
                    return flag;
                }
                else
                    return reErrorInfor;
        }
        else if (flag == FAIL)
            return overItemBean;
        return null;
    }


    /**
     * 获得所有已经答完的题目列表
     */
    public ArrayList<OverAnswerBean> getAllList(String phone)
    {
        /*String url = "SELECT\n" +
                "useroveritem.id,\n" +
                "useroveritem.phone,\n" +
                "useroveritem.item_id,\n" +
                "useroveritem.yes,\n" +
                "useroveritem.`no`,\n" +
                "useroveritem.flow,\n" +
                "useroveritem.overtime\n" +
                "FROM\n" +
                "useroveritem\n" +
                "WHERE\n" +
                "useroveritem.phone = ?";*/

        final String url = "SELECT\n" +
                "useroveritem.id,\n" +
                "useroveritem.phone,\n" +
                "useroveritem.item_id,\n" +
                "useroveritem.yes,\n" +
                "useroveritem.`no`,\n" +
                "useroveritem.flow,\n" +
                "useroveritem.overtime,\n" +
                "itemlist.img,\n" +
                "itemlist.title,\n" +
                "itemlist.answer_ids,\n" +
                "itemlist.type\n" +
                "FROM\n" +
                "useroveritem\n" +
                "INNER JOIN itemlist ON useroveritem.item_id = itemlist.item_id\n" +
                "WHERE\n" +
                "useroveritem.phone = ?\n" +
                "ORDER BY\n" +
                "useroveritem.overtime DESC";


        overAnswerBeenList = new ArrayList<>();

        DB_Utils.query(url)
                .add(phone)
                .build()
                .getAll(next ->
                {
                    OverAnswerBean bean = new OverAnswerBean();
                    bean.setId(next.getInt(1));
                    bean.setPhone(next.getString(2));
                    bean.setItemId(next.getInt(3));
                    bean.setYesNum(next.getInt(4));
                    bean.setNoNum(next.getInt(5));
                    bean.setSumFlow(next.getInt(6));
                    bean.setOvertime(next.getDateTime(7));
                    bean.setImgPath(next.getString(8));
                    bean.setTitle(next.getString(9));
                    if (!next.getString(10).isEmpty())
                        bean.setAnswer_ids(next.getString(10).split(new PropertiesUtils().get("BREAKSTRING")));
                    bean.setType(next.getInt(11));

                    overAnswerBeenList.add(bean);
                }).close();


        return overAnswerBeenList;
    }


    /**
     * 获得用户答题信息（整体）
     */
    public Map<String, String> getUserAnswerInfo(String phone)
    {
       /* String url = "SELECT\n" +
                "	COUNT(useroveritem.item_id),\n" +//答题总数
                "	SUM(CASE	WHEN useroveritem.yes = 0 THEN	1	ELSE	0 END),\n" +//正确数为0，则为错误的题目数
                "	SUM(useroveritem.flow)\n" +//总流量
                "FROM\n" +
                "	useroveritem\n" +
                "WHERE\n" +
                "	useroveritem.phone = ?";*/

        final String url = "SELECT\n" +
                "	COUNT(useroveritem.item_id),\n" +//答题总数
                "	SUM(CASE WHEN useroveritem.yes = 0 THEN 1 ELSE 0 END),\n" +//正确数为0，则为错误的题目数
                "	SUM(useroveritem.flow),\n" +//总流量
                "	(\n" +
                "		SELECT\n" +
                "			useroveritem.overtime\n" +//最后的答题时间
                "		FROM\n" +
                "			useroveritem\n" +
                "		WHERE\n" +
                "			useroveritem.phone = ?\n" +
                "		AND useroveritem.id = (\n" +
                "			SELECT\n" +
                "				MAX(useroveritem.id)\n" +
                "			FROM\n" +
                "				useroveritem\n" +
                "		)\n" +
                "	)\n" +
                "FROM\n" +
                "	useroveritem\n" +
                "WHERE\n" +
                "	useroveritem.phone = ?";

        userInfo = new HashMap<>();

        DB_Utils.query(url).add(phone).add(phone).build().get(next ->
        {
            if (next.next())
            {
                userInfo.put("SUM(OVER)", next.getInt(1) + "");//已经完成的答题数目
                userInfo.put("SUM(ERROR)", next.getInt(2) + "");//错误题目数目
                userInfo.put("SUM(FLOW)", next.getInt(3) + "");//总流量
                userInfo.put("LAST_DATATIME", next.getDateTime(4));//最后一次答题时间
            }
        }).close();

        return userInfo;
    }

    private ArrayList<AnswerOverItemBean> answerOverItemBeanList;

    /**
     * 通过时间获取列表
     */
    public ArrayList<AnswerOverItemBean> getListByDate(Date date1, Date date2)
    {
        answerOverItemBeanList = new ArrayList<>();

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd 00:00:00");

        final String url = "SELECT\n" +
                "	useroveritem.id,\n" +
                "	useroveritem.phone,\n" +
                "	useroveritem.item_id,\n" +
                "	useroveritem.yes,\n" +
                "	useroveritem.`no`,\n" +
                "	useroveritem.flow,\n" +
                "	useroveritem.overtime\n" +
                "FROM\n" +
                "	useroveritem\n" +
                "WHERE\n" +
                "	useroveritem.overtime BETWEEN ?\n" +
                "AND ?";


        DB_Utils.query(url)
                .add(date1)
                .add(date2)
                .build().getAll(next ->
        {
            AnswerOverItemBean bean = new AnswerOverItemBean();
            bean.setId(next.getInt(1));
            bean.setPhone(next.getString(2));
            bean.setItemId(next.getInt(3));
            bean.setYesNum(next.getInt(4));
            bean.setNoNum(next.getInt(5));
            bean.setSumFlow(next.getInt(6));
            bean.setOvertime(next.getDateTime(7));

            answerOverItemBeanList.add(bean);
        }).close();

        return answerOverItemBeanList;
    }


}
